Taeluf.com

user.sql

-- @query(from_email)
SELECT * FROM user WHERE email LIKE :email;

-- @query(from_cookie)
SELECT u.* FROM `user` u
    JOIN `code` c
        ON c.user_id = u.id
    WHERE 
        c.code LIKE :code
        AND c.expires_at > NOW()
        AND c.is_active = 1
        AND u.is_active = 1
        AND c.type LIKE 'login_cookie';


-- @query(code_is_valid)
SELECT u.email FROM `user` u
    JOIN `code` c
        ON c.user_id = u.id
    WHERE c.code LIKE :code
        AND c.type LIKE :type
        AND c.expires_at > NOW()
        AND c.is_active = 0;

-- @query(security_log)
INSERT INTO security_log(action, email, ip, user_agent)
VALUES (:action, :email, :ip, :user_agent);


-- @query(logout)
UPDATE `code` SET `is_active` = 0, `expires_at` = NOW()
WHERE `type` LIKE 'login_cookie' 
    AND `code` LIKE :code
    AND `user_id` = :user_id;

-- @query(get_password)
SELECT `password` FROM `user` WHERE `email` LIKE :email;


-- @query(activate, --stop)
UPDATE `code` as c
    LEFT JOIN `user` as u on u.id = c.user_id
    SET c.is_active = 1,
        c.activated_at = NOW(),
        u.is_active = 1
    
WHERE 
   c.user_id = :user_id
    AND c.is_active = 0
    AND c.activated_at IS NULL
    AND c.code LIKE :code
    AND c.expires_at > NOW()
;
SELECT c.type FROM code as c 
    WHERE c.code LIKE :code
; --stop

-- @query(new_code)
INSERT INTO `code` (code,type,user_id,expires_at,is_active,activated_at)
    VALUES(:code, :type, :user_id, TIMESTAMPADD(SECOND, :expiry, NOW()),
        :is_active, %s
     );
            

-- @query(add_role)
INSERT INTO user_role (role, user_id)
VALUES (:role, :user_id);


-- @query(all_nonrole_permissions)
SELECT action as name FROM permissions
    WHERE user_id = :user_id;


-- @query(all_roles) 
-- gets all roles even if there are no permissions associated with the role
SELECT ur.role as role, rp.action as perm FROM user_role ur
    LEFT OUTER JOIN role_permission rp
        ON rp.role LIKE ur.role
    WHERE ur.user_id = :user_id;

-- @query(get_all_roles)
SELECT ur.role as role FROM user_role ur
    WHERE ur.user_id = :user_id;

-- @query(allow)
INSERT INTO permissions (action, user_id)
    VALUES (:action, :user_id);

-- @query(deny)
DELETE FROM permissions 
    WHERE action LIKE :action
    AND user_id = :user_id;

-- @query(can)
SELECT user_id, action FROM permissions p
    WHERE p.action LIKE :action
        AND p.user_id = :user_id
UNION 
SELECT user_id, action FROM role_permission rp
    JOIN user_role ur
        on ur.role LIKE rp.role
    WHERE action LIKE :action
    AND ur.user_id = :user_id
;

-- @query(with_role)
SELECT * FROM user WHERE user.id IN 
(
    SELECT ur.user_id FROM user_role ur
        WHERE ur.role LIKE :role
)
;

-- @query(register)
INSERT INTO `user` (email, password)
    VALUES(:email, :password);

-- @query(new_password)
UPDATE user u
JOIN code c
    ON c.user_id = u.id
SET u.password = :password_hash,
    c.is_active = 1,
    c.activated_at = NOW()
WHERE 
    c.code LIKE :code 
    AND c.is_active = 0
    AND c.activated_at IS NULL
    AND c.expires_at > NOW()
    AND c.type LIKE 'password_reset'
    AND u.id = :user_id;

-- @query(get_logs)
SELECT * FROM security_log WHERE email LIKE :email ORDER BY created_at DESC LIMIT 0, %s;


-- @query(remove_role)
DELETE FROM user_role WHERE role LIKE :role AND user_id = :user_id;